-- exec sp_change_lot_product 8356, 4615, 0

ALTER PROC sp_change_lot_product

@LOT_ID 		INT, 
@NEW_PROCESSSTEP_ID 	INT, 
@FLAG 			INT 

AS
BEGIN

DECLARE @LOT_NUMBER	VARCHAR(50)
DECLARE @FAB_LOT_NUMBER VARCHAR(50)
DECLARE @LOT_STATUS	VARCHAR(50)
DECLARE @NEWLOT_STATUS 	VARCHAR(50)
DECLARE @DATECODE 	VARCHAR(50)
DECLARE @LOTCOMMENTS 	VARCHAR(2000)
DECLARE @PROCESSTYPE 	VARCHAR(50)
DECLARE @PS_TYPE	VARCHAR(50)
DECLARE @PROCESSSTEP_ID INT
DECLARE @NEWLOT_ID 	INT
DECLARE @QTYIN 		INT
DECLARE @WAFER_QTY_IN	INT
DECLARE @LOCATION_ID	INT
DECLARE @LOT_COST 	FLOAT
DECLARE @TIMESTAMP 	DATETIME
DECLARE @MAX_TIMESTAMP 	DATETIME
DECLARE @DATEIN		DATETIME
	
	SELECT @LOT_NUMBER = LOT_NUMBER, @FAB_LOT_NUMBER = FAB_LOT_NUMBER,@PROCESSSTEP_ID = PROCESSSTEP_ID,@LOCATION_ID = LOCATION_ID, @LOT_STATUS = LOT_CURRENT_STATUS, @TIMESTAMP = LOT_TABLE.TIMESTAMP FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
	SELECT @PROCESSTYPE = PS_TYPE_NAME,@PS_TYPE = PS_TYPE FROM PROCESS_STEP WHERE @PROCESSSTEP_ID = PROCESSSTEP_ID

	SELECT @NEWLOT_ID = LOT_ID, @NEWLOT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = @FAB_LOT_NUMBER AND PROCESSSTEP_ID = @NEW_PROCESSSTEP_ID AND LOCATION_ID = @LOCATION_ID
	
	IF @NEWLOT_ID IS NULL
	BEGIN
		UPDATE LOT_TABLE SET PROCESSSTEP_ID = @NEW_PROCESSSTEP_ID WHERE LOT_ID = @LOT_ID 
		UPDATE LOT_TABLE_HISTORY SET ProcessStep_ID= @NEW_PROCESSSTEP_ID WHERE LOT_ID = @LOT_ID 
	END
	ELSE
	BEGIN
		-- DUMMY UPDATE TO AVOID SQLEXCEPTION
   		UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
	END

END
